require 'mysql'

class APersistentBanana
###### API #######################################################
  
  def get_value key
    result = retrieve :ro, 'SELECT `value` FROM `store` WHERE `key` = ?', [key]
    return result[0]['value'] if result.count > 0
    raise APersistentBananaException, "Could not find value for key: " + key
  end
  
  def get_full_model type
    key = "/model/#{type}/%"
    retrieve :ro, 'SELECT `key`, `value` FROM `store` WHERE `key` LIKE ?', [key]
  end
  
  # Objects (models) as we are defining them can be identified by searching for all
  # keys whose value (ObjectType) can be found at the end of a /model/ObjectType key.
  def get_objects req = nil
    sql = "SELECT `key`, `value`, `datetime`, `author`
           FROM `store`
           WHERE `value` IN (SELECT SUBSTRING(`key`, 8)
                             FROM `store`
                             WHERE `key` IN (SELECT `key`
                                             FROM `store`
                                             WHERE `key` REGEXP '/model/[^\/]*$'))" 
    result = retrieve :ro, sql
    objects = []
    result.each do |obj|
      # we want to avoid returning core objects here
      begin
        get_value '/model/' << obj['value'] << '/core'
      rescue
        # only do our stuff if there is no /core key
        if req
          objects << obj if obj['value'] === req
        else
          objects << obj
        end
      end
    end
    return objects
  end
  
  def get_model_list
    objects = []
    result = retrieve :ro, 'SELECT `key` FROM  `store` WHERE  `key` REGEXP "/model/[^\/]*$"'
    result.each do |obj|
      begin
        get_value obj['key'] + '/core'
      rescue
        # core models are not to be listed
        objects << obj['key'].gsub('/model/', '')
      end
    end
    return objects
  end
  
  def get_record key
    result = retrieve :ro, 'SELECT * FROM `store` WHERE `key` = ?', [key]
    return result[0] if result.count > 0
  end
  
  def create model, obj, key
    # TODO: obtain user for the author value
    # TODO: error handling
    current_user = '/ahobusers/fij'
    transaction = []
    transaction << ['INSERT INTO `history`(`key`, `value`, `author`) VALUES (?, ?, ?)', [key, model.type, current_user]]
    transaction << ['INSERT INTO `store`(`key`, `value`, `author`) VALUES (?, ?, ?)', [key, model.type, current_user]]
    model.fields.each do |f|
      transaction << ['INSERT INTO `history`(`key`, `value`, `author`) VALUES (?, ?, ?)', [key + "/" + f, obj.send(f.to_sym), current_user]]
      transaction << ['INSERT INTO `store`(`key`, `value`, `author`) VALUES (?, ?, ?)', [key + "/" + f, obj.send(f.to_sym), current_user]]
    end
    execute_transaction :rw, transaction
  end
  
  def update model, obj, key
    # TODO: obtain user for the author value
    # TODO: error handling
    current_user = '/ahobusers/fij'
    transaction = []
    model.fields.each do |f|
      transaction << ['INSERT INTO `history`(`key`, `value`, `author`) VALUES (?, ?, ?)', [key + "/" + f, obj.send(f.to_sym), current_user]]
      transaction << ['UPDATE `store` SET `value` = ?, `author` = ? WHERE `key` = ?', [obj.send(f.to_sym), current_user, key + "/" + f]]
    end
    execute_transaction :rw, transaction
  end
  
#### END API ####################################################
  def initialize config
    @access = {:ro => [config.db.ro_user.name, config.db.ro_user.password], :rw => [config.db.rw_user.name, config.db.rw_user.password]}
    @host = config.db.host
    @db = config.db.name
  end
  
  def close
    # close all connections
    @conn.each {|c| c.close if c.respond_to?('close')} if @conn
  end
  
  private
  
   # queries is an array of tuples, first element is the sql, second element is an array of args (or an empty array).

  def execute_transaction privilege, queries
    # TODO: error handling & data validation
    db = connect privilege
    db.autocommit false
    queries.each do |sql, args|
      stmt = db.prepare sql
      stmt.execute *args
      stmt.close
    end
    db.commit
  end
  
  def execute privilege, sql, args = []
    # TODO: error handling & data validation
    db = connect privilege
    stmt = db.prepare sql
    stmt.execute *args
    stmt.close
  end
  
  def retrieve privilege, sql, args = []
    # TODO: error handling & data validation
    db = connect privilege
    stmt = db.prepare sql
    stmt.execute *args
    meta = stmt.result_metadata
    fieldlist = []
    meta.fetch_fields.each {|f| fieldlist << f.name}
    results = []
    stmt.each do |row|
      row_hash = {}
      fieldlist.each_with_index {|k,i| row_hash[k] = row[i]}
      results << row_hash
    end
    stmt.close
    return results
  end
  
  def connect privilege
    # if connection not loaded, create it
    # TODO: error handling
    @conn ||= {}
    @conn[privilege] = Mysql.new @host, @access[privilege][0], @access[privilege][1], @db unless @conn.has_key? privilege
    @conn[privilege]
  end
end